<?php
/* PROJECTS $Id: projects.class.php,v 1.29.4.13 2006/11/29 12:57:00 gregorerhardt Exp $ */
/**
 *	@package dotProject
 *	@subpackage modules
 *	@version $Revision: 1.29.4.13 $
 */

require_once ($AppUI->getSystemClass('dp'));
require_once ($AppUI->getLibraryClass('PEAR/Date'));
require_once ($AppUI->getModuleClass('tasks'));
require_once ($AppUI->getModuleClass('companies'));
require_once ($AppUI->getModuleClass('departments'));
require_once ($AppUI->getMasterDataClass('mdtype'));
require_once (DP_BASE_DIR . "/modules/masterdata/sequences/sequences.class.php");
/**
 * The Project Class
 */
class CProject extends CDpObject {
	var $project_id = NULL;
	var $project_company = NULL;
	var $project_department = NULL;
	var $project_name = NULL;
	var $project_short_name = NULL;
	var $project_owner = NULL;
	var $project_url = NULL;
	var $project_demo_url = NULL;
	var $project_start_date = NULL;
	var $project_end_date = NULL;
	var $project_actual_end_date = NULL;
	var $project_status = NULL;
	var $project_percent_complete = NULL;
	var $project_color_identifier = NULL;
	var $project_description = NULL;
	var $project_target_budget = NULL;
	var $project_actual_budget = NULL;
	var $project_creator = NULL;
	var $project_private = NULL;
	var $project_departments = NULL;
	var $project_contacts = NULL;
	var $project_priority = NULL;
	var $project_type = NULL;

	/**
	 * Demand Dragon fields
	 */
	var $project_code = NULL;
	var $project_rstatus = NULL;
	var $project_rtype = NULL;
	var $project_shipping_type = NULL;
	var $project_shipping_type_name = NULL;
	var $project_customer = NULL;
	var $project_customer_name = NULL;
	var $project_shipper = NULL;
	var $project_shipper_name = NULL;
	var $project_iso_code = NULL;
	var $project_date = NULL;
	var $project_currency = NULL;

	function CProject($pid = 0) {
		$joins = array(
			array( 'label' => 'Reference line', 'name' => 'project_items', 'idfield' => 'pitem_id', 'joinfield' => 'pitem_project' ),
			array( 'label' => 'Files', 'name' => 'project_files', 'idfield' => 'pfile_id', 'joinfield' => 'pfile_project' ),
			array( 'label' => 'Tasks', 'name' => 'tasks', 'idfield' => 'task_id', 'joinfield' => 'task_project' ),
			array( 'label' => 'Task log', 'name' => 'task_log', 'idfield' => 'task_log_id', 'joinfield' => 'task_log_project' ),
		);
		$this->CDpObject('projects', 'project_id', NULL, $joins, 'projects');
	}

	function check() {
		// ensure changes of state in checkboxes is captured
		$this->project_private = intval($this->project_private);

		return NULL; // object is ok
	}
	
	function loadByCode($code, $strip = true) {
		$q = new DBQuery();
		$q->addTable('projects');
		$q->addQuery('project_id');
		$q->addWhere("project_iso_code = '" . $code . "'");
		$project_id = $q->loadResult(); 
		if ($project_id) {
			return $this->load($project_id);
		}
		return false;
	}

	function canCreateTasks() {
		global $AppUI;

		if (($AppUI->user_type >= 1 && $AppUI->user_type <= 5) || ($AppUI->user_id != null && $AppUI->user_id == $this->getManager()))
			return true;

		$m = $this->getMemberIds();
		return isset ($m[$AppUI->user_id]);
	}

	function isCompleted() {
		$complete_status = new CMDStatus();
		$project_completed = dPgetSysVal( 'ProjectCompleted' );
		$complete_status->loadByCode($project_completed[0]);
		return $this->project_rstatus == $complete_status->status_id;
	}

	function canDelete(&$msg) {
		if ($this->project_rstatus != PROJECT_OPEN) {
			$msg[] = 'You only can delete open reference!';
		}
		if ($this->hasTaskOrTaskLog($this->project_id)) {
			$msg[] = 'This reference has task or task log. You can not delete it!';
		}
		if (is_array($msg) && count($msg) > 0) {
			return false;
		}
		return true;
	}

	function delete() {
		$msg = array();
		if (!$this->canDelete($msg)) {
			return $msg;
		}
		$this->load($this->project_id);
		addHistory('projects', $this->project_id, 'delete', $this->project_name, $this->project_id);
		$q = new DBQuery;
		$q->addTable('tasks');
		$q->addQuery('task_id');
		$q->addWhere("task_project = $this->project_id");
		$sql = $q->prepare();
		$q->clear();
		$tasks_to_delete = db_loadColumn($sql);
		foreach ($tasks_to_delete as $task_id) {
			$q->setDelete('user_tasks');
			$q->addWhere('task_id =' . $task_id);
			$q->exec();
			$q->clear();
			$q->setDelete('task_dependencies');
			$q->addWhere('dependencies_req_task_id =' . $task_id);
			$q->exec();
			$q->clear();
		}
		$q->setDelete('tasks');
		$q->addWhere('task_project =' . $this->project_id);
		$q->exec();
		$q->clear();
		
		$q->setDelete('task_log');
		$q->addWhere('task_log_project =' . $this->project_id);
		$q->exec();
		$q->clear();

		// remove the project-contacts and project-departments map
		$q->setDelete('project_contacts');
		$q->addWhere('project_id =' . $this->project_id);
		$q->exec();
		$q->clear();
		$q->setDelete('project_departments');
		$q->addWhere('project_id =' . $this->project_id);
		$q->exec();
		$q->clear();
		$q->setDelete('projects');
		$q->addWhere('project_id =' . $this->project_id);

		if (!$q->exec()) {
			$result = db_error();
		} else {
			$result = NULL;
		}
		$q->clear();
		return $result;
	}

	/**	Import tasks from another project
	 *
	 *	@param	int		Project ID of the tasks come from.
	 *	@return	bool
	 **/
	function importTasks($from_project_id) {

		// Load the original
		$origProject = new CProject();
		$origProject->load($from_project_id);
		$q = new DBQuery;
		$q->addTable('tasks');
		$q->addQuery('task_id');
		$q->addWhere('task_project =' . $from_project_id);
		$sql = $q->prepare();
		$q->clear();
		$tasks = array_flip(db_loadColumn($sql));

		$origDate = new CDate($origProject->project_start_date);

		$destDate = new CDate($this->project_start_date);

		$timeOffset = $destDate->getTime() - $origDate->getTime();

		// Dependencies array
		$deps = array ();

		// Copy each task into this project and get their deps
		foreach ($tasks as $orig => $void) {
			$objTask = new CTask();
			$objTask->load($orig);
			$destTask = $objTask->copy($this->project_id);
			$tasks[$orig] = $destTask;
			$deps[$orig] = $objTask->getDependencies();
		}

		// Fix record integrity
		foreach ($tasks as $old_id => $newTask) {

			// Fix parent Task
			// This task had a parent task, adjust it to new parent task_id
			if ($newTask->task_id != $newTask->task_parent)
				$newTask->task_parent = $tasks[$newTask->task_parent]->task_id;

			// Fix task start date from project start date offset
			$origDate->setDate($newTask->task_start_date);
			$destDate->setDate($origDate->getTime() + $timeOffset, DATE_FORMAT_UNIXTIME);
			$destDate = $destDate->next_working_day();
			$newTask->task_start_date = $destDate->format(FMT_DATETIME_MYSQL);

			// Fix task end date from start date + work duration
			//$newTask->calc_task_end_date();
			if (!empty ($newTask->task_end_date) && $newTask->task_end_date != '0000-00-00 00:00:00') {
				$origDate->setDate($newTask->task_end_date);
				$destDate->setDate($origDate->getTime() + $timeOffset, DATE_FORMAT_UNIXTIME);
				$destDate = $destDate->next_working_day();
				$newTask->task_end_date = $destDate->format(FMT_DATETIME_MYSQL);
			}

			// Dependencies
			if (!empty ($deps[$old_id])) {
				$oldDeps = explode(',', $deps[$old_id]);
				// New dependencies array
				$newDeps = array ();
				foreach ($oldDeps as $dep)
					$newDeps[] = $tasks[$dep]->task_id;

				// Update the new task dependencies
				$csList = implode(',', $newDeps);
				$newTask->updateDependencies($csList);
			} // end of update dependencies

			$newTask->task_percent_complete = 0;
			$newTask->store();

		} // end Fix record integrity

	} // end of importTasks

	/**
	 **	Overload of the dpObject::getAllowedRecords
	 **	to ensure that the allowed projects are owned by allowed companies.
	 **
	 **	@author	handco <handco@sourceforge.net>
	 **	@see	dpObject::getAllowedRecords
	 **/

	function getAllowedRecords($uid, $fields = '*', $orderby = '', $index = null, $extra = null) {
		$oCpy = new CCompany();

		$aCpies = $oCpy->getAllowedRecords($uid, "company_id, company_name");
		if (count($aCpies)) {
			$buffer = '(project_company IN (' .
			implode(',', array_keys($aCpies)) .
			'))';

			if ($extra['where'] != "")
				$extra['where'] = $extra['where'] . ' AND ' . $buffer;
			else
				$extra['where'] = $buffer;
		} else {
			// There are no allowed companies, so don't allow projects.
			if ($extra['where'] != '')
				$extra['where'] = $extra['where'] . ' AND 1 = 0 ';
			else
				$extra['where'] = '1 = 0';
		}

		return parent :: getAllowedRecords($uid, $fields, $orderby, $index, $extra);

	}

	function getAllowedSQL($uid, $index = null) {
		$oCpy = new CCompany();

		$where = $oCpy->getAllowedSQL($uid, "project_company");
		$project_where = parent :: getAllowedSQL($uid, $index);
		return array_merge($where, $project_where);
	}

	function setAllowedSQL($uid, & $query, $index = null) {
		$oCpy = new CCompany;
		parent :: setAllowedSQL($uid, $query, $index);
		$oCpy->setAllowedSQL($uid, $query, "project_company");
	}

	/**
	 *	Overload of the dpObject::getDeniedRecords
	 *	to ensure that the projects owned by denied companies are denied.
	 *
	 *	@author	handco <handco@sourceforge.net>
	 *	@see	dpObject::getAllowedRecords
	 */
	function getDeniedRecords($uid) {
		$aBuf1 = parent :: getDeniedRecords($uid);

		$oCpy = new CCompany();
		// Retrieve which projects are allowed due to the company rules
		$aCpiesAllowed = $oCpy->getAllowedRecords($uid, "company_id,company_name");

		$q = new DBQuery;
		$q->addTable('projects');
		$q->addQuery('project_id');
		if (count($aCpiesAllowed))
			$q->addWhere("NOT (project_company IN (" . implode(',', array_keys($aCpiesAllowed)) . '))');
		$sql = $q->prepare();
		$q->clear();
		$aBuf2 = db_loadColumn($sql);

		return array_merge($aBuf1, $aBuf2);

	}

	/** Retrieve tasks with latest task_end_dates within given project
	 * @param int Project_id
	 * @param int SQL-limit to limit the number of returned tasks
	 * @return array List of criticalTasks
	 */
	function getCriticalTasks($project_id = NULL, $limit = 1) {
		$project_id = !empty ($project_id) ? $project_id : $this->project_id;
		$q = new DBQuery;
		$q->addTable('tasks');
		$q->addWhere("task_project = $project_id AND !isnull( task_end_date ) AND task_end_date !=  '0000-00-00 00:00:00'");
		$q->addOrder('task_end_date DESC');
		$q->setLimit($limit);

		return $q->loadList();
	}

	function getManager() {
		return $this->project_owner;
	}

	/**
	 * This function return an array of project member's id
	 * @return array List of users' id who are working in this project
	 */
	function getMemberIds() {
		if ($this->project_id > 0) {
			$q = new DBQuery();
			$q->addTable('project_contacts');
			$q->addQuery('contact_id');
			$q->addWhere('project_id = ' . $this->project_id);
			return $q->loadArrayList();
		}

		return array ();
	}

	function getMembers() {
		if ($this->project_id > 0) {
			$q = new DBQuery();
			$q->addTable('project_contacts', 'pc');
			$q->addTable('contacts', 'c');
			$q->addTable('users', 'u');
			$q->addQuery('user_id');
			$q->addQuery('CONCAT_WS(" ",contact_first_name,contact_last_name)');
			$q->addOrder('contact_last_name');
			$q->addWhere('pc.contact_id = u.user_id');
			$q->addWhere('u.user_contact = c.contact_id');
			$q->addWhere('pc.project_id = ' . $this->project_id);
			return $q->loadHashList();
		}
		return array ();
	}

	/**
	 * Check if a user is a member of this project
	 * @param int user_id
	 * @return boolean result
	 */
	function hasMember($user_id) {
		if ($this->project_id > 0) {
			$q->addTable('project_contacts');
			$q->addQuery('contact_id');
			$q->addWhere('project_id = ' . $this->project_id . ' AND contact_id = ' . $user_id);
			return $q->loadResult() != false;
		}

		return false;
	}

	function getTasks() {
		if ($this->project_id > 0) {
			$q->addTable('tasks');
			$q->addWhere('task_project = ' . $this->project_id);
			$q->addOrder('task_start_date');
			return $q->loadList();
		}

		return array ();
	}

	function store() {

		$this->dPTrimAll();
		$startDate = new CDate($this->project_start_date);

		$msg = $this->check();
		if ($msg) {
			return get_class($this) . "::store-check failed - $msg";
		}

		if ($this->project_id) {
			if (!($this->hasTaskOrTaskLog($this->project_id))) {
				$tmp = explode('-', $this->project_iso_code);
				$iso_code_seq = $tmp[count($tmp) - 1];
				$tmp = array ();
				$row = new CCompany();
				$row->load($this->project_company);
				$tmp[] .= $row->company_code;
	
				$row = new CMdType();
				$row->load($this->project_shipping_type);
				$tmp[] .= $row->code;
				//$tmp[] .= $this->project_code;
				$tmp[] .= $startDate->getMonth();
				$tmp[] .= $startDate->getYear();
				$tmp[] .= $iso_code_seq;
				$this->project_iso_code = implode('-', $tmp);	
			}
			$ret = db_updateObject('projects', $this, 'project_id', false);
			addHistory('projects', $this->project_id, 'update', $this->project_name, $this->project_id);
		} else {

			//---calculate project iso code

			/*
			$q = new DBQuery;
			$q->addTable('projects');
			$q->addQuery(' count(*) + 1 as project_iso_code');
			$q->addWhere("year(project_start_date) = " . $startDate->getYear());
			*/
			// trangdt add
			$code_seq = new CSequence();
			$code_seq->seq_key = 'ref_code';
			$code_seq->loadByField();
			$this->project_code = $code_seq->useValue();
			// trangdt end

			$tmp = array ();
			$row = new CCompany();
			$row->load($this->project_company);
			$tmp[] .= $row->company_code;

			$row = new CMdType();
			$row->load($this->project_shipping_type);
			$tmp[] .= $row->code;
			//$tmp[] .= $this->project_code;
			$tmp[] .= $startDate->getMonth();
			$tmp[] .= $startDate->getYear();

			//$tmp[] .= $q->loadResult();
			// trangdt add
			$iso_code_seq = new CSequence();
			$iso_code_seq->seq_key = 'ref_iso_code';
			$iso_code_seq->seq_year = $startDate->getYear();
			$iso_code_seq->loadByField();
			$tmp[] .= $iso_code_seq->useValue();
			// trangdt end

			$this->project_iso_code = implode('-', $tmp);

			$ret = db_insertObject('projects', $this, 'project_id');
			addHistory('projects', $this->project_id, 'add', $this->project_name, $this->project_id);
		}

		//split out related departments and store them seperatly.
		$q = new DBQuery;
		$q->setDelete('project_departments');
		$q->addWhere('project_id=' . $this->project_id);
		$q->exec();
		$q->clear();
		if ($this->project_departments) {
			$departments = explode(',', $this->project_departments);
			foreach ($departments as $department) {
				$q->addTable('project_departments');
				$q->addInsert('project_id', $this->project_id);
				$q->addInsert('department_id', $department);
				$q->exec();
				$q->clear();
			}
		}

		//split out related contacts and store them seperatly.
		$q->setDelete('project_contacts');
		$q->addWhere('project_id=' . $this->project_id);
		$q->exec();
		$q->clear();
		//echo $q->prepare();
		$this->project_contacts = $_POST['project_contacts'];
		if ($this->project_contacts) {
			$contacts = is_array($this->project_contacts) ? $this->project_contacts : explode(',', $this->project_contacts);
			foreach ($contacts as $contact) {
				if ($contact) {
					$q->addTable('project_contacts');
					$q->addInsert('project_id', $this->project_id);
					$q->addInsert('contact_id', $contact);
					$q->exec();
					$q->clear();
				}
			}
		}

		if (!$ret) {
			return get_class($this) . "::store failed <br />" . db_error();
		} else {
			return NULL;
		}

	}
	// trangdt add
	function canComplete() {
		global $AppUI;
		$reasons = array ();

		$q->createTemp('otherexpense');
		$q->addTable('task_log');
		$q->addQuery('SUM(task_log_amount) as sum_amount');
		$q->addWhere('task_log_expense = 3');
		$q->addWhere('task_log_project = ' . $this->project_id);
		$otherExprense = $q->loadResult();
		$q->clear();

		//main query for tasks
		$q->addTable('tasks');
		$q->leftJoin('quotations', 'q', 'quote_id = task_quotation');
		$q->addWhere('task_project = ' . $this->project_id);
		$q->addWhere('((task_quotation_version > 0 && (task_quotation_version < quote_version || quote_completed < 1)) or task_quotation IS NULL or task_quotation = 0 or task_percent_complete < 100)');
		$q->addOrder('task_start_date desc, task_name');
		$q->addQuery('distinct tasks.*');

		$list = $q->loadHashList('task_id');
		$q->dropTemp('pitems, otherexpense');
		$reasons = NULL;
		if (count($list) > 0) {
			$reasons = 'There are ' . count($list) . ' tasks need to complete';
		}
		if ($otherExprense > 0) {
			if ($reasons == NULL) {
				$reasons = '';
			} else {
				$reasons .= '<br/>';
			}
			$reasons .= 'There are task log has other expense ['. $otherExprense .']';
		}
		return $reasons;
	}

	function canForceComplete($warnings='') {
		global $AppUI;
		$reasons = array ();
		
		$q = new DBQuery();
//		$q->addTable('task_log');
//		$q->addQuery('SUM(task_log_amount) as sum_amount');
//		$q->addWhere('task_log_expense = 3');
//		$q->addWhere('task_log_project = ' . $this->project_id);
//		$otherExprense = $q->loadResult();
//		$q->clear();

		//main query for tasks
		$q->addTable('tasks');
		$q->leftJoin('quotations', 'q', 'quote_id = task_quotation');
		$q->addWhere('task_project = ' . $this->project_id);
		$q->addWhere('((task_quotation_version > 0 && (task_quotation_version < quote_version || quote_completed < 1)) or task_quotation IS NULL or task_quotation = 0 or task_is_complete = \'false\')');
		$q->addOrder('task_start_date desc, task_name');
		$q->addQuery('distinct tasks.*, q.*');
		$list = $q->loadList();
		
		$q->clear();
		$q->addTable('task_log');
		$q->addWhere('task_log_project = ' . $this->project_id);
		$q->addWhere('(task_log_is_complete = \'false\' or task_log_expense = 3)');
		$q->addQuery('distinct task_log.*');
		$logList = $q->loadList();

		$reasons = NULL;
		$errors = array();
		$warns = array();
		if (count($list) > 0) {
			foreach ($list as $row) {
				if (intval($row['task_quotation']) > 0 && (intval($row['task_quotation_version']) < $row['quote_version'] || $row['quote_completed'] < 1)) {
					$errors[] .= 'Task [' . $row['task_description'] . '] link with quotation [' . $row['quote_code'] . '] is inactive';
				}
				//(task_quotation IS NULL or task_quotation = 0 or task_percent_complete < 100 or (sum_amount IS NOT NULL AND sum_amount > 0))');
				if (!isset($row['task_quotation']) || $row['task_quotation'] < 1) {
					$errors[] .= 'Task [' . $row['task_description'] . '] not link with any quotation';
				}
				if (intval($row['task_is_complete']) <= 0) {
					$errors[] .= 'Task [' . $row['task_description'] . '] is not complete';
				}
			}
		}
		if (count($logList) > 0) {
			foreach ($logList as $row) {
				if (intval($row['task_log_is_complete']) <= 0) {
					$errors[] .= 'Task Log [' . $row['task_log_description'] . '] is not complete';
				}
				if ($row['task_log_expense'] == 3) {
					$errors[] .= 'Task Log [' . $row['task_log_description'] . '] has Other Expense';
				}
			}
		}
//			if ($otherExprense > 0) {
//				$errors[] .= 'Reference has other expense ' . $row['sum_amount'];
//			}
		if (count($errors)) {
			$reasons = '<p>Reference can not complete because: <br/><font color="#FF0000">' . implode('<br/>', $errors) . '</font></p>';
		}
		if (count($warns)) {
			$warnings = '<p>Warnings:<br/> <font color="#000000">' . implode('<br/>', $warns) . '</font></p>';
		}
		
//		$q->dropTemp('pitems');
//		$q->exec();
//		$q->clear();
		
		return $reasons;
	}

	function doForceComplete($completed_date = 0) {
		$project_id = $this->project_id;
		$this->project_actual_end_date = substr($completed_date, 0, 4) . '-'.substr($completed_date, 4, 2) . '-'.substr($completed_date, 6, 2) . ' 00:00:00';
		require_once(DP_BASE_DIR."/modules/masterdata/mdstatus.class.php");
		$complete_status = new CMDStatus();
		$project_completed = dPgetSysVal( 'ProjectCompleted' );
		$complete_status->loadByCode($project_completed[0]);
		
		$this->project_rstatus = $complete_status->status_id;
		if (($msg = $this->store())) {
			return $msg;
		}
		try {
			$sql = "update tasks set task_is_complete=1, task_percent_complete=100 where task_project = $project_id";
			db_exec($sql);
			
			$sql = "update projects set project_percent_complete=100 where project_id = $project_id";
			db_exec($sql);	
		} catch (Exception $e) {
			return $e->getMessage();
		}
		return '';		
	}
	
	function canProcess() {
		return NULL;
	}

	function doArchive() {
		db_exec("insert into projects_archived select * from projects WHERE project_id = " . $this->project_id);
		db_exec("insert into project_files_archived select * from project_files WHERE pfile_project = " . $this->project_id);
		db_exec("insert into project_items_archived select * from project_items WHERE pitem_project = " . $this->project_id);
		db_exec("insert into tasks_archived select * from tasks WHERE task_project = " . $this->project_id);
		db_exec("insert into task_log_archived select * from task_log WHERE task_log_project = " . $this->project_id);
		db_exec("insert into user_tasks_archived select ut.* from user_tasks ut INNER JOIN tasks t ON ut.task_id = t.task_id WHERE task_project = " . $this->project_id);
		db_exec("delete from projects WHERE project_id = " . $this->project_id);
		db_exec("delete from project_files WHERE pfile_project = " . $this->project_id);
		db_exec("delete from project_items WHERE pitem_project = " . $this->project_id);
		db_exec("delete t.*, ut.* from tasks t LEFT JOIN user_tasks ut ON ut.task_id = t.task_id WHERE task_project = " . $this->project_id);
		return db_error();
	}

	function canEdit(){
		global $AppUI, $m;
		require_once(DP_BASE_DIR."/modules/masterdata/mdstatus.class.php");
		$perms =& $AppUI->acl();
		$canEdit = $perms->checkModuleItem( $m, 'edit', $this->project_id ) || $AppUI->user_type == SYSADMIN;
		$complete_status = new CMDStatus();
		$project_completed = dPgetSysVal( 'ProjectCompleted' );
		$complete_status->loadByCode($project_completed[0]);
		$canEdit = $canEdit && $this->project_rstatus != $complete_status->status_id;
		return $canEdit;
	}

	function initDBQuery($limit = 100, $start = 0) {
		global $AppUI;
		$perms =& $AppUI->acl();
		$hasRoleAccounting = $perms->userHasRole($AppUI->user_id, 'Accounting');
		
		$q = new DBQuery();
//		$q->dropTemp('shipping_types, project_types');
//		$q->exec();
//		$q->clear();

//		$q->createTemp('shipping_types');
//		$q->addQuery('type_id as stype_id, name as shipping_type_name');
//		$q->addTable('md_type');
//		$q->addWhere("type = 'ShippingType'");
//		$q->exec();
//		$q->clear();
//
//		$q->createTemp('project_types');
//		$q->addQuery('type_id as ptype_id, name as project_type_name');
//		$q->addTable('md_type');
//		$q->addWhere("type = 'Reference'");
//		$q->exec();
//		$q->clear();


		$q->addTable('projects');

		if ($hasRoleAccounting) {
			$q->innerJoin('tasks', 'ptasks', 'project_id = task_project');
			$q->innerJoin('task_log', 'plogs', 'project_id = task_log_project');
		}
		
//		$q->innerJoin('shipping_types', 'stypes', 'stype_id = project_shipping_type');
//		$q->innerJoin('project_types', 'ptypes', 'ptype_id = project_rtype');
		$q->innerJoin('suppliers', '', 'sup_id = project_shipper');
		$q->innerJoin('md_status', 'status', 'status_id = project_rstatus');
		$q->innerJoin('companies', 'com', 'company_id = project_company');
		$q->setLimit($limit, $start);
		return $q;
	}

	function getList($crit, &$totalRows, $limit = 100, $start = 0) {
		$q = $this->initDBQuery($limit, $start);

		$company = dPgetParam($crit, 'project_company');
		$this->filterByCustomer($company, $q);

		$shipper = dPgetParam($crit, 'project_shipper');
		$this->filterByShipper($shipper, $q);

		$status = dPgetParam($crit, 'project_rstatus');
		$complete_from_date = dPgetParam($crit, 'complete_from_date');
		$complete_to_date = dPgetParam($crit, 'complete_to_date');
		$this->filterByStatus($status, $q, $complete_from_date, $complete_to_date);

		$minSalesAmount = dPgetParam($crit, 'min_project_sales_amount');
		$maxSalesAmount = dPgetParam($crit, 'max_project_sales_amount');
		$this->filterBySalesAmount($minSalesAmount, $maxSalesAmount, $q, $complete_from_date, $complete_to_date);
		
		$expenseType = dPgetParam($crit, 'expense_type');
		$this->filterByExpenseAmount($q, $expenseType, $complete_from_date, $complete_to_date);

		$project_name = dPgetParam($crit, 'project_name');
		if ($project_name != '') {
			$q->addWhere("project_name LIKE '%$project_name%'");
		}
		$project_code = dPgetParam($crit, 'project_code');
		if ($project_code != '') {
			$q->addWhere("project_code LIKE '%$project_code%'");
		}
//		$project_rtype = dPgetParam($crit, 'project_rtype');
//		if ($project_rtype) {
//			$q->addWhere("project_rtype = $project_rtype");
//		}
		$project_iso_code = dPgetParam($crit, 'project_iso_code');
		if ($project_iso_code != '') {
			$q->addWhere("project_iso_code LIKE '%$project_iso_code%'");
		}
		$from_date = dPgetParam($crit, 'from_date');
		if ($from_date != '') {
			$q->addWhere("project_start_date >= '$from_date'");
		}
		$to_date = dPgetParam($crit, 'to_date');
		if ($to_date != '') {
			$q->addWhere("project_start_date <= '$to_date'");
		}
//		$complete_from_date = dPgetParam($crit, 'complete_from_date');
//		if ($complete_from_date != '') {
//			$q->addWhere("project_actual_end_date >= $complete_from_date");
//		}
//		$complete_to_date = dPgetParam($crit, 'complete_to_date');
//		if ($complete_to_date != '') {
//			$q->addWhere("project_actual_end_date <= $complete_to_date");
//		}
		$keyword = dPgetParam($crit, 'keyword');
		if ($keyword != '') {
			$q->addWhere("(project_iso_code LIKE '%$keyword%' or project_name LIKE '%$keyword%')");
		}

		$qTotal = $q->prepare();
		$qTotal = str_ireplace('select *', 'select count(distinct(projects.project_id)) as total_rows', $qTotal);
		$totalRows = db_loadResult($qTotal);
		
		global $AppUI;
		$perms =& $AppUI->acl();
		$hasRoleAccounting = $perms->userHasRole($AppUI->user_id, 'Accounting');
		$q->addQuery('distinct projects.*, suppliers.*, com.*');
		if ($hasRoleAccounting) {
			$q->addQuery('sa.*');
			$q->addQuery('ea.*');
		}
		
		$q->addOrder('project_company');
//		echo $q->prepare();
//		exit();
		return $q->loadList();
	}

	function filterByCustomer($company, & $q ) {
		if ($company) {
			$q->addWhere("project_company = $company");
		}
	}

	function filterByShipper($shipper, & $q ) {
		if ($shipper) {
			$q->addWhere("project_company = $shipper");
		}
	}

	function filterByStatus($status, & $q, $completedDateFrom = NULL, $completedDateTo = NULL ) {
		global $AppUI;
		$perms =& $AppUI->acl();
		$hasRoleAccounting = $perms->userHasRole($AppUI->user_id, 'Accounting');
		if ($hasRoleAccounting) {
			//for tasks
			$sql = array();
			$sql[] = '(task_is_complete = \'true\'';
			if ($completedDateFrom) {
				$sql[] = " $completedDateFrom <= task_end_date "; 
			}
			if ($completedDateTo) {
				$sql[] = " $completedDateTo >= task_end_date "; 
			}
			$s1 = implode(' AND ', $sql) . ')';
			
			//for task logs
			$sql = array();
			$sql[] = '(task_log_is_complete = \'true\'';
			if ($completedDateFrom) {
				$sql[] = " $completedDateFrom <= task_log_completed_date "; 
			}
			if ($completedDateTo) {
				$sql[] = " $completedDateTo >= task_log_completed_date "; 
			}
			$s1 .= ' OR ' . implode(' AND ', $sql) . ')';
			
			//add where
			$q->addWhere("($s1)");
		}
		if ($status) {
			$q->addWhere("project_rstatus = $status");
		}
	}

	function filterByExpenseAmount(&$q, $expenseType = NULL, $completedDateFrom = NULL, $completedDateTo = NULL) {
		global $AppUI;
		$perms =& $AppUI->acl();
		$hasRoleAccounting = $perms->userHasRole($AppUI->user_id, 'Accounting');
		if (!$hasRoleAccounting) {
			return;
		}
		$t = new DBQuery();
		$t->dropTemp('p_expense_amount');
		$t->exec();

		$t->clear();
		$t->createTemp('p_expense_amount');
		$t->addTable('task_log');
		$t->addQuery('task_log_project as ea_project, SUM(task_log_amount_origin) as ea_sum');
		if ($expenseType == NULL) {
			$t->addWhere('task_log_expense <> 3');
		} else {
			$t->addWhere("task_log_expense = $expenseType");
		}
		if ($hasRoleAccounting) {
			$t->addWhere('task_log_is_complete = \'true\'');
			if ($completedDateFrom) {
				$t->addWhere(" $completedDateFrom <= task_log_completed_date "); 
			}
			if ($completedDateTo) {
				$t->addWhere(" $completedDateTo >= task_log_completed_date "); 
			}
		}
		$t->addGroup('task_log_project');
		$t->exec();

		$q->addJoin('p_expense_amount', 'ea', 'ea_project = project_id');
	}
	
	function filterBySalesAmount($minSalesAmount, $maxSalesAmount, & $q, $completedDateFrom = NULL, $completedDateTo = NULL) {
		global $AppUI;
		$perms =& $AppUI->acl();
		$hasRoleAccounting = $perms->userHasRole($AppUI->user_id, 'Accounting');
		$canViewSalesAmount = $perms->checkModuleItem('taskfee', 'view');
		if (!$canViewSalesAmount && !$hasRoleAccounting) {
			return;
		}
		$t = new DBQuery();
		$t->dropTemp('p_sales_amount');
		$t->exec();

		$t->clear();
		$t->createTemp('p_sales_amount');
		$t->addTable('tasks');
		$t->addQuery('task_project as sa_task_project, SUM(task_fee) as sa_sales_amount, SUM(task_vat_amount) as sa_vat_amount');
		if ($hasRoleAccounting) {
			$t->addWhere('task_is_complete = \'true\'');
			if ($completedDateFrom) {
				$t->addWhere(" $completedDateFrom <= task_end_date "); 
			}
			if ($completedDateTo) {
				$t->addWhere(" $completedDateTo >= task_end_date "); 
			}
		}
		$t->addGroup('task_project');
		$t->exec();

		$q->addJoin('p_sales_amount', 'sa', 'sa_task_project = project_id');
		if ($minSalesAmount) {
			$q->addWhere("sa_sales_amount >= $minSalesAmount");
		}
		if ($maxSalesAmount) {
			$q->addWhere("sa_sales_amount <= $maxSalesAmount");
		}
	}

	function hasTaskOrTaskLog($project_id) {
		$q = new DBQuery();
		$q->addTable('tasks');
		$q->addQuery('count(*)');
		$q->addWhere("task_project = $project_id");
		$count = $q->loadResult();
		
		$q->clear();
		$q->addTable('task_log');
		$q->addQuery('count(*)');
		$q->addWhere("task_log_project = $project_id");
		$count += $q->loadResult();
		
		return $count > 0;
	}
	
	function getCompletedPercent($project_id) {
		$working_hours = ($dPconfig['daily_working_hours']?$dPconfig['daily_working_hours']:8);
		$q = new DBQuery();
		$q->addTable('projects');
	    $q->addQuery(' SUM(t1.task_duration * t1.task_percent_complete'
					.' * IF(t1.task_duration_type = 24, '.$working_hours.', t1.task_duration_type))'
					.' / SUM(t1.task_duration * IF(t1.task_duration_type = 24, '.$working_hours.', t1.task_duration_type))'
					.' AS project_percent_complete');
	    $q->addJoin('companies', 'com', 'company_id = project_company');
	    $q->addJoin('users', 'u', 'user_id = project_owner');
	    $q->addJoin('suppliers', 'sup', 'sup_id = project_shipper');
	    $q->addJoin('contacts', 'con', 'contact_id = user_contact');
	    $q->addJoin('tasks', 't1', 'projects.project_id = t1.task_project');
	    $q->addWhere('project_id = '.$project_id .' AND t1.task_id = t1.task_parent');
	    $q->addGroup('project_id');
	    $sql = $q->prepare();
	    return $q->loadResult();
	}

	/**
	 * Lay mang gia tri expense cua mot project.
	 * Params:
	 * 		project_id
	 * 		expense_type: 	NULL: all
	 * 						1: Inter expense
	 * 						2: On behalf expense
	 * 						3: Other expense
	 *
	 * Return:
	 * 		Expense amount
	 */
	function getExpenseAmount($project_id, $expense_type = NULL, $currencies = NULL) {
		$q = new DBQuery();
		$q->addTable('task_log');

		if ($currencies == NULL) {
			$q->addQuery('SUM(task_log_amount)');
		} else {
			$sql = "CASE task_log_currency ";
			foreach($currencies as $k => $v) {
				$sql .= "WHEN $k THEN $v ";
			}
			$sql .= " ELSE 1 END";
			$q->addQuery('SUM((' . $sql . ') * task_log_amount_origin)');
		}

		$q->addWhere('task_log_project = '.$project_id);
		if ($expense_type != NULL) {
			$q->addWhere("task_log_expense = $expense_type");
		}
		$behalfExpense =$q->loadResult();
		return rtrim($behalfExpense, '.');
	}

	function getSalesAmount($project_id) {
		$q = new DBQuery();
		$q->addTable('tasks');
		$q->addQuery('SUM(task_fee)');
		$q->addWhere('task_project = '.$project_id);
		$vat =$q->loadResult();
		return rtrim($vat, '.');
	}

	function getVATAmount($project_id) {
		$q = new DBQuery();
		$q->addTable('tasks');
		$q->addQuery('SUM(task_vat_amount)');
		$q->addWhere('task_project = '.$project_id);
		$vat =$q->loadResult();
		return rtrim($vat, '.');
	}

	function getAccountReceivable($project_id) {
		$q = new DBQuery();
		$q->addTable('tasks');
		$q->addQuery('SUM(task_fee + task_vat_amount)');
		$q->addWhere('task_project = '.$project_id);
		$vat =$q->loadResult();
		return rtrim($vat, '.');
	}

	function getExpenseGroup($project_id, $expense_type = NULL, $currencies = NULL, $completedDateFrom = NULL, $completedDateTo = NULL) {
		$q = new DBQuery();
		$q->addTable('task_log');
		$q->addQuery('ty.code as item_group, SUM(task_log_amount_origin) as expense');

		$q->innerJoin('items', 'i', 'task_log_item = item_id');
		$q->innerJoin('md_type', 'ty', 'type_id = item_type2');
		$q->addWhere('task_log_project = '.$project_id);
		$q->addGroup('ty.code');
		if ($expense_type != NULL) {
			$q->addWhere("task_log_expense = $expense_type");
		}
		if ($completedDateFrom) {
			$q->addWhere(" $completedDateFrom <= task_log_completed_date "); 
		}
		if ($completedDateTo) {
			$q->addWhere(" $completedDateTo >= task_log_completed_date "); 
		}
		return $q->loadHashList();
	}

	function getAccountReceivableGroup($project_id, $completedDateFrom = NULL, $completedDateTo = NULL) { 
		$q = new DBQuery();
		$q->addTable('tasks');
		$q->addQuery('ty.code as item_group, SUM(task_fee + task_vat_amount) as ar');

		$q->innerJoin('items', 'i', 'task_item = item_id');
		$q->innerJoin('md_type', 'ty', 'type_id = item_type2');
		$q->addWhere('task_project = '.$project_id);
		if ($completedDateFrom) {
			$q->addWhere(" $completedDateFrom <= task_log_completed_date "); 
		}
		if ($completedDateTo) {
			$q->addWhere(" $completedDateTo >= task_log_completed_date "); 
		}
		$q->addGroup('ty.code');
		return $q->loadHashList();
	}
}

/* The next lines of code have resided in projects/index.php before
 ** and have been moved into this 'encapsulated' function
 ** for reusability of that central code.
 **
 ** @date 20060225
 ** @responsible gregorerhardt
 **
 ** E.g. this code is used as well in a tab for the admin/viewuser site
 **
 ** @mixed user_id 	userId as filter for tasks/projects that are shown, if nothing is specified,
 current viewing user $AppUI->user_id is used.
 */

function projects_list_data($user_id = false) {
	global $AppUI, $addPwOiD, $buffer, $company, $company_id, $company_prefix, $deny, $department, $dept_ids, $dPconfig, $orderby, $orderdir, $projects, $tasks_critical, $tasks_sum, $tasks_summy, $tasks_total, $owner;

	$addProjectsWithAssignedTasks = $AppUI->getState('addProjWithTasks') ? $AppUI->getState('addProjWithTasks') : 0;

	// get any records denied from viewing
	$obj = new CProject();
	$deny = $obj->getDeniedRecords($AppUI->user_id);

	// Let's delete temproary tables
	$q = new DBQuery;
	$q->dropTemp('tasks_sum, tasks_total, tasks_summy, tasks_critical, tasks_users, shipping_types');
	$q->exec();
	$q->clear();

	// Task sum table
	// by Pablo Roca (pabloroca@mvps.org)
	// 16 August 2003

	$working_hours = ($dPconfig['daily_working_hours'] ? $dPconfig['daily_working_hours'] : 8);

	// GJB: Note that we have to special case duration type 24 and this refers to the hours in a day, NOT 24 hours
	$q->createTemp('tasks_sum');
	$q->addTable('tasks');
	$q->addQuery("task_project, SUM(task_duration * task_percent_complete * IF(task_duration_type = 24, {$working_hours}," .
	" task_duration_type)) / SUM(task_duration * IF(task_duration_type = 24, {$working_hours}," .
	" task_duration_type)) AS project_percent_complete, SUM(task_duration * IF(task_duration_type = 24," .
	" {$working_hours}, task_duration_type)) AS project_duration");
	if ($user_id) {
		$q->addJoin('user_tasks', 'ut', 'ut.task_id = tasks.task_id');
		$q->addWhere('ut.user_id = ' . $user_id);
	}
	$q->addWhere("tasks.task_id = tasks.task_parent");
	$q->addGroup('task_project');
	$tasks_sum = $q->exec();
	$q->clear();

	// Task total table
	$q->createTemp('tasks_total');
	$q->addTable('tasks');
	$q->addQuery("task_project, COUNT(distinct tasks.task_id) AS total_tasks");
	if ($user_id) {
		$q->addJoin('user_tasks', 'ut', 'ut.task_id = tasks.task_id');
		$q->addWhere('ut.user_id = ' . $user_id);
	}
	$q->addGroup('task_project');
	$tasks_total = $q->exec();
	$q->clear();

	// temporary My Tasks
	// by Pablo Roca (pabloroca@mvps.org)
	// 16 August 2003
	$q->createTemp('tasks_summy');
	$q->addTable('tasks');
	$q->addQuery('task_project, COUNT(distinct task_id) AS my_tasks');
	if ($user_id) {
		$q->addWhere('task_owner = ' . $user_id);
	} else {
		$q->addWhere('task_owner = ' . $AppUI->user_id);
	}
	$q->addGroup('task_project');
	$tasks_summy = $q->exec();
	$q->clear();

	// temporary critical tasks
	$q->createTemp('tasks_critical');
	$q->addTable('tasks');
	$q->addQuery('task_project, task_id AS critical_task, MAX(task_end_date) AS project_actual_end_date');
	$q->addJoin('projects', 'p', 'p.project_id = task_project');
	$q->addOrder("task_end_date DESC");
	$q->addGroup('task_project');
	$tasks_critical = $q->exec();
	$q->clear();

	if ($addProjectsWithAssignedTasks) {
		// temporary users tasks
		$q->createTemp('tasks_users');
		$q->addTable('tasks');
		$q->addQuery('task_project');
		$q->addQuery('ut.user_id');
		$q->addJoin('user_tasks', 'ut', 'ut.task_id = tasks.task_id');
		if ($user_id) {
			$q->addWhere('ut.user_id = ' . $user_id);
		}
		$q->addOrder("task_end_date DESC");
		$q->addGroup('task_project');
		$tasks_users = $q->exec();
		$q->clear();
	}

	// add Projects where the Project Owner is in the given department
	if ($addPwOiD && isset ($department)) {
		$owner_ids = array ();
		$q->addTable('users');
		$q->addQuery('user_id');
		$q->addJoin('contacts', 'c', 'c.contact_id = user_contact');
		$q->addWhere('c.contact_department = ' . $department);
		$owner_ids = $q->loadColumn();
		$q->clear();
	}

	if (isset ($department)) {
		//If a department is specified, we want to display projects from the department, and all departments under that, so we need to build that list of departments
		$dept_ids = array ();
		$q->addTable('departments');
		$q->addQuery('dept_id, dept_parent');
		$q->addOrder('dept_parent,dept_name');
		$rows = $q->loadList();
		addDeptId($rows, $department);
		$dept_ids[] = $department;
	}
	$q->clear();
	if (true) {
		$q->createTemp('shipping_types');
		$q->addTable('md_type');
		$q->addQuery('p.project_id as shipping_type_project, type_id, name as shipping_type_name');
		$q->addJoin('projects', 'p', 'p.project_shipping_type = type_id');
		$q->addWhere("type = 'ShippingType'");
		$q->addWhere("active = '1'");
		$q->exec();
		$q->clear();
	}

	// retrieve list of records
	// modified for speed
	// by Pablo Roca (pabloroca@mvps.org)
	// 16 August 2003
	// get the list of permitted companies
	$obj = new CCompany();
	$companies = $obj->getAllowedRecords($AppUI->user_id, 'company_id,company_name', 'company_name');
	if (count($companies) == 0)
		$companies = array (
			0
		);

	$q->addTable('projects');
	/*$q->addQuery('projects.project_id, project_status, project_color_identifier, project_name, project_description, project_duration,
		project_start_date, project_end_date, project_color_identifier, project_company, company_name, company_description, project_status,
		project_priority, tc.critical_task, tc.project_actual_end_date, tp.task_log_problem, tt.total_tasks, tsy.my_tasks,
		ts.project_percent_complete, user_username');*/
	$q->addQuery('projects.*, stypes.shipping_type_name as project_shipping_type_name, company_name, company_description, project_status,
			project_priority, tc.critical_task, tc.project_actual_end_date, tt.total_tasks, tsy.my_tasks,
			ts.project_percent_complete, user_username');
	$q->addJoin('companies', 'com', 'projects.project_company = company_id');
	$q->addJoin('users', 'u', 'projects.project_owner = u.user_id');
	$q->addJoin('tasks_critical', 'tc', 'projects.project_id = tc.task_project');
	$q->addJoin('tasks_sum', 'ts', 'projects.project_id = ts.task_project');
	$q->addJoin('tasks_total', 'tt', 'projects.project_id = tt.task_project');
	$q->addJoin('tasks_summy', 'tsy', 'projects.project_id = tsy.task_project');
	$q->addJoin('shipping_types', 'stypes', 'projects.project_id = stypes.shipping_type_project');
	if ($addProjectsWithAssignedTasks)
		$q->addJoin('tasks_users', 'tu', 'projects.project_id = tu.task_project');
	// DO we have to include the above DENY WHERE restriction, too?
	//$q->addJoin('', '', '');
	if (isset ($department)) {
		$q->addJoin('project_departments', 'pd', 'pd.project_id = projects.project_id');
	}
	if (!isset ($department) && $company_id && !$addPwOiD) {
		$q->addWhere("projects.project_company = '$company_id'");
	}
	if (isset ($department) && !$addPwOiD) {
		$q->addWhere("pd.department_id in ( " . implode(',', $dept_ids) . " )");
	}
	if ($user_id && $addProjectsWithAssignedTasks) {
		$q->addWhere('(tu.user_id = ' . $user_id . ' OR projects.project_owner = ' . $user_id . ' )');
	}
	elseif ($user_id) {
		$q->addWhere('projects.project_owner = ' . $user_id);
	}
	if ($owner > 0)
		$q->addWhere('projects.project_owner = ' .
		$owner);

	// Show Projects where the Project Owner is in the given department
	if ($addPwOiD && !empty ($owner_ids))
		$q->addWhere('projects.project_owner IN (' .
		implode(',', $owner_ids) . ')');

	$q->addGroup('projects.project_id');
	$q->addOrder("$orderby $orderdir");
	$obj->setAllowedSQL($AppUI->user_id, $q);
	$projects = $q->loadList();

	// get the list of permitted companies
	$companies = arrayMerge(array (
		'0' => $AppUI->_('All'
	)), $companies);

	//get list of all departments, filtered by the list of permitted companies.
	$q->clear();
	$q->addTable('companies');
	$q->addQuery('company_id, company_name, dep.*');
	$q->addJoin('departments', 'dep', 'companies.company_id = dep.dept_company');
	$q->addOrder('company_name,dept_parent,dept_name');
	$obj->setAllowedSQL($AppUI->user_id, $q);
	$rows = $q->loadList();

	//display the select list
	$buffer = '<select name="department" onChange="document.pickCompany.submit()" class="text">';
	$buffer .= '<option value="company_0" style="font-weight:bold;">' . $AppUI->_('All') . '</option>' . "\n";
	$company = '';
	foreach ($rows as $row) {
		if ($row["dept_parent"] == 0) {
			if ($company != $row['company_id']) {
				$buffer .= '<option value="' . $company_prefix . $row['company_id'] . '" style="font-weight:bold;"' . ($company_id == $row['company_id'] ? 'selected="selected"' : '') . '>' . $row['company_name'] . '</option>' . "\n";
				$company = $row['company_id'];
			}
			if ($row["dept_parent"] != null) {
				showchilddept($row);
				findchilddept($rows, $row["dept_id"]);
			}
		}
	}
	$buffer .= '</select>';

}
?>
